using System;
using System.Data;
using System.Data.Common;

namespace System.Data.DBLib
{
	/// <summary>
	/// Summary description for Class1.
	/// </summary>
	public class CDBLib
    {
        #region Variables
        public static int	DATA_TYPE_TABLE = 1;
		public static int	DATA_TYPE_SP = 2;
		protected string	m_strConnection;
		protected string	m_strUserID;
		protected string	m_strPwd;
		protected string	m_strDatabase;
		protected string	m_strServer;
		protected string	m_strMessage;
        protected DbCommand m_objCommand = null;
        protected DbConnection m_objConnection = null;
        #endregion

        #region Constrcutor
        public CDBLib()
		{
			//
			// TODO: Add constructor logic here
			//
        }
        #endregion

        #region Connection
        virtual public bool connect(string strUserID, string strPwd, string strDatabase, string strServer)
		{
            if (strPwd.Trim() == "")
            {
                return connect(strDatabase, strServer);
            }
            else
            {
                m_strUserID = strUserID;
                m_strPwd = strPwd;
                m_strDatabase = strDatabase;
                m_strServer = strServer;
                m_strConnection = "Data Source=";
                m_strConnection += m_strServer;
                m_strConnection += ";Initial Catalog=";
                m_strConnection += m_strDatabase;
                m_strConnection += ";User Id=";
                m_strConnection += m_strUserID;
                m_strConnection += ";Password=";
                m_strConnection += m_strPwd;
                m_strConnection += ";";
                m_objConnection = getConnection();

                try
                {
                    if (m_objConnection != null)
                    {
                        m_objConnection.Open();
                    }
                    else
                    {
                        m_strMessage = "Connection is not created";
                        return false;
                    }
                }
                catch (Exception e)
                {
                    m_strMessage = e.Message;
                    return false;
                }
            }

			return true;
		}

        virtual public bool connect(string strDatabase, string strServer)
        {
            m_strDatabase = strDatabase;
            m_strServer = strServer;
            m_strConnection = "Persist Security Info=False;Integrated Security=true;Initial Catalog=" + m_strDatabase + ";server=" + m_strServer;
            m_objConnection = getConnection();

            try
            {
                if (m_objConnection != null)
                {
                    m_objConnection.Open();
                }
                else
                {
                    m_strMessage = "Connection is not created";
                    return false;
                }
            }
            catch (Exception e)
            {
                m_strMessage = e.Message;
                return false;
            }

            return true;
        }

		virtual public bool connect(string strDatabase)
		{
			m_strDatabase = strDatabase;
			m_strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + m_strDatabase;
            m_objConnection = getConnection(m_strConnection);

            try
            {
                if (m_objConnection != null)
                {
                    m_objConnection.Open();
                }
                else
                {
                    m_strMessage = "Connection is not created";
                    return false;
                }
            }
            catch (Exception e)
            {
                m_strMessage = e.Message;
                return false;
            }
			
			return true;
        }

        public bool isConnected()
        {
            if (m_objConnection != null)
            {
                if (m_objConnection.State == ConnectionState.Open)
                {
                    return true;
                }

                m_objConnection.Close();
                m_objConnection = null;
            }

            return false;
        }
        #endregion

        #region Data Set
        virtual public DataSet getDataSet(DataSet ds, string strSql, string strDataSetName, string strRecordName)
		{
            return getDataSet(ds, strSql, strDataSetName, strRecordName, 0);
		}

		virtual public DataSet getDataSet(DataSet ds, string strSql, string strDataSetName, string strRecordName, int nTimeout)
		{
            if (m_objConnection != null)
            {
                if (ds == null)
                {
                    ds = new DataSet(strDataSetName);
                }

                try
                {
                    DbDataAdapter da = getAdapter(strSql);
                    da.SelectCommand.CommandTimeout = nTimeout;
                    da.Fill(ds, strRecordName);
                }
                catch (Exception e)
                {
                    m_strMessage = "Error: " + e.Message;
                }
            }
            else
            {
                return null;
            }

            return ds;
		}

		virtual public DataSet getDataSet(DataSet ds, string strSql, string strData, string strDataSetName, string strRecordName)
		{
            int i = 0;

            if (m_objConnection != null)
            {
                if (ds == null)
                {
                    ds = new DataSet(strDataSetName);
                }

                try
                {
                    DbDataAdapter da = getAdapter(strSql);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    addReturnParameter("RETURN_VALUE", 0);
                    addParameter("xmldata", strData, 0, 8);
                    addIntParameter("errcode");
                    addOutputParameter("msg");
                    da.Fill(ds, strRecordName);
                    i = (Int32)da.SelectCommand.Parameters["@RETURN_VALUE"].Value;
                }
                catch (Exception e)
                {
                    m_strMessage = "Error in updating SPData. Message: " + e.Message;
                }
            }
            else
            {
                return null;
            }

            return ds;
		}

		virtual public DataSet getDataSet(string strSql, string strRecordName)
		{
            DataSet ds = null;

            return getDataSet(ds, strSql, "Data", strRecordName);
		}

        virtual public DataSet getDataSet(string[] strSql, string[] strRecordName)
        {
            DataSet ds = new DataSet();

            for (int i = 0; i < strSql.Length && i < strRecordName.Length; i++)
            {
                ds = getDataSet(ds, strSql[i], "Data", strRecordName[i]);
            }

            return ds;
        }
        #endregion

        #region Data Table
        virtual public DataTable getDataTable(string strSql, string strRecordName)
		{
            DataSet ds = getDataSet(strSql, strRecordName);

            if (ds != null)
            {
                try
                {
                    return ds.Tables[strRecordName];
                }
                catch (Exception e)
                {
                    m_strMessage = "Error in updating SPData. Message: " + e.Message;
                }
            }

            return null;
        }
        #endregion

        #region Data Row
        virtual public DataRow getDataRow(string strSql, string strRecordName)
		{
            DataTable dt = getDataTable(strSql, strRecordName);

            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0];
            }

            return null;
        }
        #endregion

        #region Update
        virtual public int updateData(string strSql, CDBError objErr)
		{
            if (m_objConnection != null)
            {
                m_objCommand = getCommand(strSql);

                try
                {
                    m_objCommand.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    m_strMessage = e.Message;
                    objErr.Message = m_strMessage;
                    objErr.RetCode = -1;
                }
            }
            else
            {
                m_strMessage = "Connection is not created.";
                objErr.Message = m_strMessage;
                objErr.RetCode = -1;
            }

            return objErr.RetCode;
		}
        #endregion

        #region Statement
        virtual public int prepareStatement(string strSql)
		{
            if (m_objConnection != null)
            {
                m_objCommand = getCommand(strSql);
                m_objCommand.CommandType = CommandType.StoredProcedure;
                addReturnParameter("RETURN_VALUE", 0);
            }
            else
            {
                m_strMessage = "Connection is not created.";
                return 1;
            }

            return 0;
        }

        virtual public int updateData(string strSql, string strData, CDBError objErr)
        {
            prepareStatement(strSql);
            addParameter("xmldata", strData, 0, 8);

            return executeStatement(objErr, true);
        }

        public int executeStatement()
        {
            CDBError objError = new CDBError();

            return executeStatement(objError);
        }

        public int executeStatement(CDBError objErr)
        {
            return executeStatement(objErr, false);
        }

		virtual public int executeStatement(CDBError objErr, bool bMsg)
		{
            if (m_objConnection != null && m_objCommand != null)
            {
                try
                {
                    if (bMsg)
                    {
                        addIntParameter("errcode");
                        addOutputParameter("msg");
                    }

                    m_objCommand.ExecuteNonQuery();

                    objErr.RetCode = (Int32)m_objCommand.Parameters["@RETURN_VALUE"].Value;

                    if (bMsg)
                    {
                        objErr.ErrCode = (Int32)m_objCommand.Parameters["@errcode"].Value;
                        objErr.Message = (string)m_objCommand.Parameters["@msg"].Value;
                    }
                }
                catch (Exception e)
                {
                    m_strMessage += "DB Update Error: " + e.Message;
                    objErr.Message = m_strMessage;
                    objErr.RetCode = -1;
                }
                finally
                {
                    m_objCommand = null;
                }
            }
            else
            {
                m_strMessage = "Connection is not created.";
                objErr.Message = m_strMessage;
                objErr.RetCode = -1;
            }

            return objErr.RetCode;
		}

		public DataSet executeQueryStatement(CDBError objErr)
        {
            return executeQueryStatement(objErr, false);
        }

        public DataSet executeQueryStatement(CDBError objErr, bool bMsg)
        {
            DataSet ds = new DataSet("Data");

            if (bMsg)
            {
                addIntParameter("errcode");
                addOutputParameter("msg");
            }

            return executeQueryStatement(ds, "Record", objErr);
        }

        virtual public DataSet executeQueryStatement(DataSet ds, string strTable, CDBError objErr)
		{
            if (m_objConnection != null && m_objCommand != null)
            {
                try
                {
                    if (ds == null)
                    {
                        ds = new DataSet("Data");
                    }

                    DbDataAdapter da = getAdapter();

                    da.Fill(ds, strTable);
                    objErr.RetCode = (Int32)m_objCommand.Parameters["@RETURN_VALUE"].Value;

                    return ds;
                }
                catch (Exception e)
                {
                    m_strMessage = "Error in updating SPData. Message: " + e.Message;
                    objErr.Message = m_strMessage;
                    objErr.RetCode = -1;
                }
                finally
                {
                    m_objCommand = null;
                }
            }
            else
            {
                m_strMessage = "Connection is not created.";
                objErr.Message = m_strMessage;
                objErr.RetCode = -1;
            }

            return null;
		}

        public DbDataReader openStatement(CDBError objErr)
        {
            if (m_objConnection != null && m_objCommand != null)
            {
                try
                {
                    return m_objCommand.ExecuteReader();
                }
                catch (Exception e)
                {
                    m_strMessage = e.Message;
                    objErr.Message = m_strMessage;
                    objErr.RetCode = -1;
                }
            }
            else
            {
                m_strMessage = "Connection is not created.";
                objErr.Message = m_strMessage;
                objErr.RetCode = -1;
            }

            return null;
        }
        #endregion

        #region Parameter
        virtual public DbParameter addParameter(string strFieldName, int nSize, int nType)
        {
            return null;
        }

        virtual public int addParameter(string strFieldName, object objValue, int nSize, int nType)
        {
            int i = 0;

            if (m_objCommand != null)
            {
                DbParameter objParam = addParameter(strFieldName, nSize, nType);

                if (objParam != null)
                {
                    objParam.Value = objValue;
                }
            }
            else
            {
                m_strMessage = "Connection is not created.";
                i = 1;
            }

            return i;
        }

        virtual public int addParameter(string strFieldName, string strValue)
		{
            return addParameter(strFieldName, strValue, strValue.Length * 2);
		}

		virtual public int addParameter(string strFieldName, string strValue, int n)
		{
            return addParameter(strFieldName, strValue, n, 1);
		}

		virtual public int addParameter(string strFieldName, int nValue)
		{
            return addParameter(strFieldName, nValue, 0, 0);
		}

		virtual public int addParameter(string strFieldName, Int64 nValue)
		{
            return addParameter(strFieldName, nValue, 0, 5);
		}

		virtual public int addParameter(string strFieldName, decimal dValue)
		{
            return addParameter(strFieldName, dValue, 0, 6);
		}

		virtual public int addParameter(string strFieldName, double dValue)
		{
            return addParameter(strFieldName, dValue, 0, 2);
		}

		virtual public int addParameter(string strFieldName, DateTime dtValue)
		{
            return addParameter(strFieldName, dtValue, 0, 4);
		}

        virtual public int addParameter(string strFieldName, byte[] bytValue)
        {
            return addParameter(strFieldName, bytValue, 0, 7);
        }

        virtual public void addOutputParameter(string strFieldName, string strValue, int nType)
        {
            if (m_objCommand != null)
            {
                DbParameter objParam = addParameter(strFieldName, 50, nType);

                if (objParam != null)
                {
                    if (nType == 1 && strValue != null)
                    {
                        objParam.Value = strValue;
                    }

                    objParam.Direction = ParameterDirection.Output;
                }
            }
            else
            {
                m_strMessage = "Connection is not created.";
            }
        }

        virtual public void addOutputParameter(string strFieldName)
        {
            addOutputParameter(strFieldName, null);
        }

        virtual public void addOutputParameter(string strFieldName, string strValue)
		{
            addOutputParameter(strFieldName, strValue, 1);
		}

		virtual public void addIntParameter(string strFieldName)
		{
            addOutputParameter(strFieldName, null, 0);
		}

		virtual public void addInt64Parameter(string strFieldName)
		{
            addOutputParameter(strFieldName, null, 5);
		}

        virtual public void addReturnParameter(string strFieldName, int nType)
        {
            if (m_objCommand != null)
            {
                DbParameter objParam = addParameter(strFieldName, 50, nType);

                if (objParam != null)
                {
                    objParam.Direction = ParameterDirection.ReturnValue;
                }
            }
            else
            {
                m_strMessage = "Connection is not created.";
            }
        }

        virtual public object getParameter(string strFieldName)
		{
            if (m_objCommand != null)
            {
                return m_objCommand.Parameters["@" + strFieldName].Value;
            }

            return null;
        }

        protected ParameterDirection getParameterType(int nType)
		{
			if (nType == 0)
			{
				return ParameterDirection.Input;
			}
			else
			{
				return ParameterDirection.Output;
			}
        }
        #endregion

        #region Close
        virtual public void resetCommand()
		{
            m_objCommand = null;
		}

		virtual public bool close()
		{
            try
            {
                if (m_objConnection != null)
                {
                    m_objConnection.Close();
                }

                return true;
            }
            catch (Exception e)
            {
                m_strMessage = e.Message;
            }

            return false;
        }
        #endregion

        #region Message
        public void setMessage(string strMessage)
		{
			m_strMessage = strMessage;
		}

		public string getLastError()
		{
			return m_strMessage;
        }
        #endregion

        #region XML
        public string getXMLData(DataSet ds)
		{
			if (ds != null)
			{
				return ds.GetXml();
			}

			return "";
        }
        #endregion

        #region Conversion
        public int GetIntValue(DataRow dr, string strField)
		{
			try
			{
				return Convert.ToInt32(dr[strField]);
			}
			catch(Exception)
			{
			}

			return 0;
		}

        public long GetLongValue(DataRow dr, string strField)
        {
            try
            {
                return Convert.ToInt64(dr[strField]);
            }
            catch (Exception)
            {
            }

            return 0;
        }

		public string GetStringValue(DataRow dr, string strField)
		{
			try
			{
				return (string)dr[strField];
			}
			catch(Exception)
			{
			}

			return "";
		}

		public double GetDoubleValue(DataRow dr, string strField)
		{
			try
			{
				return Convert.ToDouble(dr[strField]);
			}
			catch(Exception)
			{
			}

			return 0;
		}

		public DateTime GetDateTimeValue(DataRow dr, string strField)
		{
			try
			{
				return Convert.ToDateTime(dr[strField]);
			}
			catch(Exception)
			{
			}

			return new DateTime(1900, 1, 1);
        }
        #endregion

        #region Function
        public virtual DbConnection getConnection()
        {
            return getConnection(m_strConnection);
        }

        public virtual DbConnection getConnection(string strConnection)
        {
            return null;
        }

        public DbCommand getCommand()
        {
            return m_objCommand;
        }

        public virtual DbCommand getCommand(string strSql)
        {
            return null;
        }

        public virtual DbDataAdapter getAdapter(string strSql)
        {
            return null;
        }

        public virtual DbDataAdapter getAdapter()
        {
            return null;
        }

        public virtual DbDataReader getReader(string strSql)
        {
            m_objCommand = getCommand(strSql);

            return m_objCommand.ExecuteReader();
        }

        public void setCommandTimeOut(int nSec)
        {
            m_objCommand.CommandTimeout = nSec;
        }
        #endregion
    }
}
